﻿Imports System.Data
Imports System.Data.Odbc
Imports System.Text
Imports System.Collections.Generic
Imports WSEntidades
Imports Utilerias
Imports WSFactory
Imports Npgsql
Imports MySql.Data.MySqlClient

Public Class ImpLocalDAL
    Private cadConex As String = ""

    Public Sub New(ByVal pCadConex As String)
        Me.cadConex = pCadConex
    End Sub

#Region "FuncionesUtilizables"

    Public Function existeImpLocal(ByVal pImpuesto As String) As Boolean
        Dim id As Int16 = 0
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT COUNT(*) " & _
                                            "FROM impLocal " & _
                                            "WHERE Impuesto = ?Impuesto;"
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("?Impuesto", pImpuesto)
                    myConn.Open()
                    id = Convert.ToInt16(myComm.ExecuteScalar())
                End Using
            End Using
            Return (id > 0)
        Catch ex As Exception
            Throw
        Finally
            id = Nothing
        End Try
    End Function

    Public Function registraImpLocal(ByVal pImpLocal As ImpLocalBE) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "INSERT INTO implocal(Impuesto, Tasa, Tipo, Status, AfectaIVA) " & _
                                            "VALUES (@Impuesto, @Tasa, @Tipo, @Status, @AfectaIVA);"
                    myComm.CommandType = System.Data.CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Impuesto", pImpLocal.Impuesto)
                    myComm.Parameters.AddWithValue("@Tasa", pImpLocal.Tasa)
                    Select Case pImpLocal.Tipo
                        Case ImpLocalBE.TipoImpLocal.Traslado
                            myComm.Parameters.AddWithValue("@Tipo", "T")
                        Case ImpLocalBE.TipoImpLocal.Retencion
                            myComm.Parameters.AddWithValue("@Tipo", "R")
                    End Select
                    Select Case pImpLocal.Status
                        Case ImpLocalBE.EstatusImpLocal.Activa
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case ImpLocalBE.EstatusImpLocal.Inactiva
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    Select Case pImpLocal.AfectaIVA
                        Case ImpLocalBE.AfectaIVAImpLocal.Si
                            myComm.Parameters.AddWithValue("@AfectaIVA", "S")
                        Case ImpLocalBE.AfectaIVAImpLocal.No
                            myComm.Parameters.AddWithValue("@AfectaIVA", "N")
                    End Select
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function actualizaImpLocal(ByVal pImpLocal As ImpLocalBE, ByRef msg As String) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT count(*) " &
                                            "FROM implocal " & _
                                            "WHERE IdImpLocal <> @id " &
                                                "AND  Impuesto = @descripcion;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@id", pImpLocal.IdImpLocal)
                    myComm.Parameters.AddWithValue("@descripcion", pImpLocal.Impuesto)
                    myConn.Open()
                    If (Convert.ToInt16(myComm.ExecuteScalar()) > 0) Then
                        msg &= "Existe otro registro con el Impuesto especificado." & vbCrLf
                        Return False
                    End If

                    myComm.CommandText = "UPDATE implocal " & _
                                            "SET Impuesto = @Impuesto, " & _
                                                "Tasa = @Tasa, " & _
                                                "Tipo = @Tipo, " & _
                                                "Status = @Status, " & _
                                                "AfectaIVA = @AfectaIVA " & _
                                            "WHERE IdImpLocal = @IdImpLocal; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    myComm.Parameters.AddWithValue("@Impuesto", pImpLocal.Impuesto)
                    myComm.Parameters.AddWithValue("@Tasa", pImpLocal.Tasa)
                    Select Case pImpLocal.Tipo
                        Case ImpLocalBE.TipoImpLocal.Traslado
                            myComm.Parameters.AddWithValue("@Tipo", "T")
                        Case ImpLocalBE.TipoImpLocal.Retencion
                            myComm.Parameters.AddWithValue("@Tipo", "R")
                    End Select
                    Select Case pImpLocal.Status
                        Case ImpLocalBE.EstatusImpLocal.Activa
                            myComm.Parameters.AddWithValue("@Status", "A")
                        Case ImpLocalBE.EstatusImpLocal.Inactiva
                            myComm.Parameters.AddWithValue("@Status", "I")
                        Case Else
                            myComm.Parameters.AddWithValue("@Status", "I")
                    End Select
                    Select Case pImpLocal.AfectaIVA
                        Case ImpLocalBE.AfectaIVAImpLocal.Si
                            myComm.Parameters.AddWithValue("@AfectaIVA", "S")
                        Case ImpLocalBE.AfectaIVAImpLocal.No
                            myComm.Parameters.AddWithValue("@AfectaIVA", "N")
                    End Select
                    myComm.Parameters.AddWithValue("@IdImpLocal", pImpLocal.IdImpLocal)
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function cambiaStatus(ByVal pId As Integer, ByVal pSetActivo As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE implocal " & _
                                            "SET Status = @Status " & _
                                            "WHERE IdImpLocal = @IdImpLocal; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (pSetActivo) Then
                        myComm.Parameters.AddWithValue("@Status", "A")
                    Else
                        myComm.Parameters.AddWithValue("@Status", "I")
                    End If
                    myComm.Parameters.AddWithValue("@IdImpLocal", pId)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function cambiaAfectaIVA(ByVal pId As Integer, ByVal pAfectaIVA As Boolean) As Boolean
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "UPDATE implocal " & _
                                            "SET AfectaIVA = @AfectaIVA " & _
                                            "WHERE IdImpLocal = @IdImpLocal; "
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.Clear()
                    If (pAfectaIVA) Then
                        myComm.Parameters.AddWithValue("@AfectaIVA", "S")
                    Else
                        myComm.Parameters.AddWithValue("@AfectaIVA", "N")
                    End If
                    myComm.Parameters.AddWithValue("@IdImpLocal", pId)
                    myConn.Open()
                    If (myComm.ExecuteNonQuery() = 1) Then
                        Return True
                    Else
                        Return False
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        End Try
    End Function

    Public Function getImpLocal(ByVal IdImpLocal As Integer) As ImpLocalBE
        Dim tmpImpLocal As ImpLocalBE
        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdImpLocal, Impuesto, Tasa, Tipo, Status, AfectaIVA " & _
                                            "FROM implocal " & _
                                            "WHERE IdImpLocal = @IdImpLocal;"
                    myComm.CommandType = CommandType.Text
                    myComm.Parameters.AddWithValue("@IdImpLocal", IdImpLocal)
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        If (myDR.HasRows()) Then
                            myDR.Read()
                            tmpImpLocal = ImpLocalFact.Build(myDR)
                        Else
                            tmpImpLocal = Nothing
                        End If
                    End Using
                End Using
            End Using
            Return tmpImpLocal

        Catch ex As Exception
            Throw
        Finally
            tmpImpLocal = Nothing
        End Try
    End Function

    Public Function getListadoImpLocal(ByVal tipoImpuesto As Enumeraciones.tipoImpuestosLocalesParaBusquedas,
                                ByVal tipoStatus As Enumeraciones.estatusParaBusquedas, ByVal tipoAffIVA As Enumeraciones.tipoAfectaIVAParaBusquedas) As List(Of ImpLocalBE)
        Dim ImpLocalLs As List(Of ImpLocalBE) = New List(Of ImpLocalBE)
        Dim strQryFiltros As String = ""

        If (tipoImpuesto <> Enumeraciones.tipoImpuestosLocalesParaBusquedas.Todos Or
            tipoStatus <> Enumeraciones.estatusParaBusquedas.Todos Or
            tipoAffIVA <> Enumeraciones.tipoAfectaIVAParaBusquedas.Todos) Then
            strQryFiltros = " WHERE "
            If (tipoImpuesto <> Enumeraciones.tipoImpuestosLocalesParaBusquedas.Todos) Then
                Select Case tipoImpuesto
                    Case Enumeraciones.tipoImpuestosLocalesParaBusquedas.Traslado
                        strQryFiltros &= "Tipo = 'T' "
                    Case Enumeraciones.tipoImpuestosLocalesParaBusquedas.Retencion
                        strQryFiltros &= "Tipo = 'R' "
                End Select
                strQryFiltros &= "AND "
            End If
            If (tipoStatus <> Enumeraciones.estatusParaBusquedas.Todos) Then
                Select Case tipoStatus
                    Case Enumeraciones.estatusParaBusquedas.Activos
                        strQryFiltros &= " Status = 'A' "
                    Case Enumeraciones.estatusParaBusquedas.Inactivos
                        strQryFiltros &= " Status = 'I' "
                End Select
                strQryFiltros &= "AND "
            End If
            If (tipoAffIVA <> Enumeraciones.tipoAfectaIVAParaBusquedas.Todos) Then
                Select Case tipoAffIVA
                    Case Enumeraciones.tipoAfectaIVAParaBusquedas.AfectaIVA
                        strQryFiltros &= " AfectaIVA = 'S' "
                    Case Enumeraciones.tipoAfectaIVAParaBusquedas.NoAfectaIVA
                        strQryFiltros &= " AfectaIVA = 'N' "
                End Select
            End If

            If (strQryFiltros.EndsWith("AND ")) Then
                strQryFiltros = strQryFiltros.Substring(0, strQryFiltros.Length - 4)
            End If
        End If

        Try
            Using myConn As MySqlConnection = New MySqlConnection(Me.cadConex)
                Using myComm As MySqlCommand = myConn.CreateCommand()
                    myComm.CommandText = "SELECT IdImpLocal, Impuesto, Tasa, Tipo, Status, AfectaIVA " & _
                                           "FROM implocal " &
                                           strQryFiltros & ";"
                    myComm.CommandType = CommandType.Text
                    myConn.Open()
                    Using myDR As MySqlDataReader = myComm.ExecuteReader()
                        While myDR.Read()
                            Dim ImpLocal As ImpLocalBE = ImpLocalFact.Build(myDR)
                            If (Not ImpLocal Is Nothing) Then
                                ImpLocalLs.Add(ImpLocal)
                            End If
                            ImpLocal = Nothing
                        End While
                    End Using
                End Using
            End Using
            Return ImpLocalLs
        Catch ex As Exception
            Throw
        Finally
            ImpLocalLs = Nothing
            strQryFiltros = Nothing
        End Try
    End Function
#End Region

End Class
